package com.awifi.demo.excel;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @description: ToDo
 * @author: marin
 * @time: 2020/8/18 20:22
 */
public class TransExcelToSql {

    static public String  outFileDir = "C:/Users/chengliang/Desktop/sql/";
    static public String inputFilePath = "C:/Users/chengliang/Desktop/设备115.xlsx";
    static String share_customer_id = "1151";


    public static void main( String[] args )
    {
        createSql();
    }


    static public void createSql() {

        //获取当前时间
        Date currentTime = new Date();
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String dateString = formatter.format(currentTime);

        //生成对应文件名称的sql文件
        String outputFilePath = inputFilePath.substring(inputFilePath.lastIndexOf("/")+1, inputFilePath.lastIndexOf("."));
        String outSqlFileName = outFileDir + outputFilePath +".sql";

        List<Object> cityResult = ExcelUtil.readXlsxFileToArray(inputFilePath);

        System.out.println(cityResult.size());

        if(cityResult == null){
            System.out.println("read  excel failed");
            return;
        }

        System.out.println("excel date row count:" + cityResult.size());

        Object o = cityResult.get(0);
        System.out.println(o);
        String  appSql = null;
        StringBuilder stringBuilder = new StringBuilder();

        for(Object ob:cityResult) {
            /**
             * 取一行数据
             */
            @SuppressWarnings("unchecked")
            ArrayList<String> arr = (ArrayList<String>) ob;

            /**
             * 取特定列的值
             */
            String costumer_id = "qzzzbxj20200616";
            String deviceNum = arr.get(0);


            appSql = "INSERT INTO `awifi_capacity`.`capacity_vedio_customer_device_shared`(`share_customer_id`, `original_customer_id`, `device_num`, `share_permission`, `create_by`, `create_date`) VALUES ";
            appSql += " (" + share_customer_id + ",";
            appSql += " ("+ "SELECT d.customer_id FROM capacity_vedio_device d WHERE d.device_num='"+deviceNum+"'"+"),";
            appSql += "'"+deviceNum+"',";

            appSql += "NULL,'admin',";
            appSql += "'"+dateString+"'"+"); \n";
            stringBuilder.append(appSql);
        }

        System.out.println(stringBuilder);
        WriteFile.writeFile(outSqlFileName, stringBuilder);
    }
}
